package org.pet.mapper;

import org.apache.ibatis.annotations.*;
import org.pet.entity.PetSubVariety;
import org.pet.entity.PetVariety;
import org.pet.util.Result;

import java.util.List;

@Mapper
public interface PetVarietyMapper {

    //查询一级宠物种类
    @Select("select * from pet_variety")
    List<PetVariety> list();
    //根据一级宠物种类id查询二级宠物种类
    @Select("select * from pet_subvariety where variety = #{id}")
    List<PetSubVariety> subList(Integer id);
    //根据二级宠物种类获取对应一级宠物种类
    @Select("select variety from pet_variety where id = (select id from pet_subvariety where category = #{category})")
    String getVariety(String category);

    @Select("select id from pet_variety where variety = #{variety}")
    Integer getVarietyId(String variety);
    //新增一级目录
    @Insert("insert into pet_variety values (null,#{variety})")
    int add(PetVariety petVariety);
    //新增二级目录
    @Insert("insert into pet_subvariety values (null,#{category},#{variety},#{pic})")
    int subAdd(PetSubVariety petSubVariety);

    //根据id删除一级目录
    @Delete("delete from pet_variety where id=#{id}")
    int delete(Integer id);
    //根据id删除二级目录
    @Delete("delete from pet_subvariety where id=#{id}")
    int subDelete(Integer id);
    //根据variety删除二级目录
    @Delete("delete from pet_subvariety where variety=#{id}")
    int subDeleteAll(Integer id);

    //根据id更新一级目录
    @Update("update pet_variety set variety=#{variety} where id=#{id}")
    int update(PetVariety petVariety);
    //根据id更新二级目录
    @Update({
            "<script>",
            "update pet_subvariety",
            "<set>",
            "<if test='category != null'>category = #{category},</if>",
            "<if test='variety != null'>variety = #{variety},</if>",
            "<if test='pic != null'>pic = #{pic},</if>",
            "</set>",
            "where id = #{id}",
            "</script>"})
    int subUpdate(PetSubVariety petSubVariety);

    @Select("select * from pet_subvariety where category like CONCAT('%', #{search}, '%');")
    List<PetSubVariety> searchList(String search);

    @Select("select a.* from pet_subvariety a where not exists( select 1 from pet b where b.name = a.category)")
    List<PetSubVariety> searchNotExit();
}
